﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.Security;
using WebMatrix.WebData;

/// <summary>
/// Summary description for ClassName
/// </summary>
public class AccountManager
{
    public AccountManager()
    {
        //
        // TODO: Add constructor logic here
        //
    }
    
    public static bool AddUser(string user,string pass){
        try{
          string sql = @"SELECT Email FROM UserProfile WHERE LOWER(Email) = LOWER(@0)";
            object[] param = new object[]{user};
            var u = DBManager.Query(sql,param);
            if(u.Count==0){
                //insert into UserProfile
                DBManager.Execute("INSERT INTO UserProfile (Email) VALUES (@0)",param);
                //create user in membership account
                WebSecurity.CreateAccount(user, pass, false);
            }
            return true;
        }catch(Exception e){
            return false;
        }
       
    }
    
    public static bool RemoveUser(int userid){
        try{
            //first remove  user from any role
            string sql_role = @"DELETE FROM webpages_UsersInRoles WHERE UserId=@0";
            object[] param1 = new object[]{userid};
            DBManager.Execute(sql_role,param1);
            
            string sql = @"DELETE FROM UserProfile WHERE UserId=@0";
            object[] param = new object[]{userid};
            DBManager.Execute(sql,param);
            return true;
        }catch(Exception e){
            throw e;
            return false;
        }
    }
    
    
    public static bool AddUserToRole(int userid,int roleid){
        try{
            string sql = @"SELECT UserId FROM webpages_UsersInRoles WHERE UserId=@0 AND RoleId=@1";
            object[] param = new object[2]{userid,roleid};
            var u = DBManager.Query(sql,param);
            if(u.Count==0){
                //insert user in role
                var res=DBManager.Execute("INSERT INTO webpages_UsersInRoles(UserId,RoleId) VALUES (@0,@1)",param);
            }
            
            return true;
        }catch(Exception e){
            return false;
        }
        
    }
    
    public static bool RemoveUserInRole(int userid,int roleid){
        try{
            string sql = @"DELETE FROM webpages_UsersInRoles WHERE UserId=@0 AND RoleId=@1";
            object[] param = new object[2]{userid,roleid};
            var res = DBManager.Execute(sql,param);
            return true;
        }catch(Exception e){
            return false;
        }
    }
    
    public static bool AddRole(string role){
        try{
            string sql = @"SELECT RoleId FROM webpages_Roles WHERE RoleName=@0";
            object[] param = new object[]{role};
            var r = DBManager.Query(sql,param);
            if(r.Count==0){
                //inser role
                DBManager.Execute("INSERT INTO webpages_Roles(RoleName) VALUES(@0)",param);
            }
            return true;
        }catch(Exception e){
            return false;
        }
    }
    
    public static bool RemoveRole(int r){
        try{
            string sql = @"DELETE FROM webpages_Roles WHERE RoleId=@0";
            object[] param = new object[]{r};
            var res = DBManager.Execute(sql,param);
            return true;
        }catch(Exception e){
            return false;
        }
    }
    
    public static IList<dynamic> GetRoleMembers(int role){
        try{
            string sql = @"SELECT * FROM UserProfile u,webpages_UsersInRoles r
                          WHERE u.UserId = r.UserId
                          AND r.RoleId = @0 ORDER BY u.Email ASC";
            
            object[] param = new object[]{role};
            var r = DBManager.Query(sql,param);
            return r;
        }catch(Exception e){
            throw e;
        }
    }
    
    public static IList<dynamic> GetRoles(){
        try{
            string sql = @"SELECT * FROM webpages_Roles ORDER BY RoleName ASC ";
            var res = DBManager.Query(sql,null);
            return res;
        }catch(Exception e){
            throw e;
        }
    }
    
    public static IList<dynamic> GetUsers(){
        try{
            string sql = @"SELECT * FROM UserProfile";
            var res = DBManager.Query(sql,null);
            return res;
        }catch(Exception e){
            throw e;
        }
    }
    
    public static IList<dynamic> GetUserRoles(int id){
        try{
            var sql = @"SELECT r.RoleId,r.RoleName FROM webpages_Roles r,webpages_UsersInRoles ur 
                      WHERE r.RoleId=ur.RoleId
                      AND ur.UserId=@0 ORDER BY r.RoleName ASC";
            object[] param = new object[]{id};
            var r = DBManager.Query(sql,param);
            return r;
        }catch(Exception e){
            throw e;
        }
    }
    
    public static IList<dynamic> GetData(string param,int type){
        try{
            switch(type){
                case 1:
                    return GetUsers();
                    break;
                case 2:
                    return GetRoles();
                    break;
                case 3:
                    return GetUserRoles(Convert.ToInt32(param));
                    break;
            }
            return null;
        }catch(Exception e){
           throw e;            
        }
    }
    
    
    
    
    
    
}
